﻿CREATE TRIGGER tr_Loan_Verification_CustomInsert
ON dbo.t_Loan_Verification
AFTER INSERT
AS

IF @@ROWCOUNT <> 1 RETURN

DECLARE @VID int,@LoanID varchar(50),@Verifier sysname,@ParentID int,@Lable sysname

SELECT @LoanID = LoanID,
			 @ParentID = ParentID,
			 @Lable = Lable,
			 @Verifier = dbo.Verifier(LoanID)
FROM inserted WHERE IsCustom = 1

IF @@ROWCOUNT = 0 OR @Verifier = dbo.UserName() RETURN

SELECT @VID = MIN(VerificationID) FROM t_Loan_Verification WHERE LoanID = @LoanID

DECLARE @Path nvarchar(4000),@SQL VARCHAR(128)
SET @SQL = 'SELECT Lable FROM fverTree_Parents('+@LoanID+','+STR(@ParentID)+') ORDER BY Pos DESC'
EXEC sp__Join @SQL,'/',@Path OUTPUT

DECLARE @Subject nvarchar(4000)
SET @Subject = 'New Item ['+ISNULL(@Path+'/'+@Lable,'No path')+'] in '+LTRIM(@LoanID)+' from '+dbo.UserName()
DECLARE @Text nvarchar(4000)
SET @Text = dbo.config_URL_ShowLoan()+CONVERT(varchar(10),@LoanID)

EXEC s_Mail @Verifier,@Subject,@Text

